package com.lpf.excel;

import cn.hutool.core.util.ReflectUtil;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * 功能描述：Excel导出工具类
 *
 * @author lpf
 * @date 2020/03/03 14:04
 * @version: 3.0
 **/
@SuppressWarnings("all")
public class ExcelUtil {
	private static HSSFWorkbook wb;

	private static CellStyle titleStyle;        // 标题行样式
	private static Font titleFont;              // 标题行字体
	private static CellStyle dateStyle;         // 日期行样式
	private static Font dateFont;               // 日期行字体
	private static CellStyle headStyle;         // 表头行样式
	private static Font headFont;               // 表头行字体
	private static CellStyle contentStyle;      // 内容行样式
	private static Font contentFont;            // 内容行字体

	/**
	 * 将Map里的集合对象数据输出Excel数据流
	 */
	public static void export2Excel(List<SheetInfo> sheetInfos, OutputStream outputStream) throws Exception {
		init();

		HSSFSheet[] sheets = getSheets(sheetInfos); // 创建所有的sheet表

		for (int i = 0; i < sheetInfos.size(); i++) {

			SheetInfo sheetInfo = sheetInfos.get(i);
			HSSFSheet sheet = sheets[i];

			createTableTitleRow(sheetInfo, sheet);  // 标题行
			createTableDateRow(sheetInfo, sheet);   // 日期行
			creatTableHeadRow(sheetInfo, sheet);    // 表头

			List<Object> datas = sheetInfo.getDatas();
			for (int j = 0; j < datas.size(); j++) {

				HSSFRow contentRow = sheets[i].createRow(j + 3);
				contentRow.setHeight((short) 300);
				HSSFCell[] cells = getCells(contentRow, sheetInfo.getHeadNames().size());
				int cellNum = 1;                    // 去掉一列序号，因此从1开始

				Object data = datas.get(j);

				if (data instanceof List) {
					List<String> values = Collections.unmodifiableList((List<String>) data);
					for (Object value : values) {
						cells[cellNum++].setCellValue(value == null ? "" : value.toString());
					}
				} else {
					for (String fieldName : sheetInfo.getFieldNames()) {
						Object value = ReflectUtil.getFieldValue(data, fieldName);
						cells[cellNum].setCellValue(value == null ? "" : value.toString());
						cellNum++;
					}
				}
			}
			adjustColumnSize(sheetInfo, sheet);    // 自动调整列宽
		}

		wb.write(outputStream);
	}

	/**
	 * 初始化标题、列名称、表格内容等的字体、样式
	 */
	private static void init() {
		wb = new HSSFWorkbook();

		titleFont = wb.createFont();
		titleStyle = wb.createCellStyle();
		dateFont = wb.createFont();
		dateStyle = wb.createCellStyle();
		headFont = wb.createFont();
		headStyle = wb.createCellStyle();
		contentFont = wb.createFont();
		contentStyle = wb.createCellStyle();

		initTitleCellStyle();
		initTitleFont();
		initDateCellStyle();
		initDateFont();
		initHeadCellStyle();
		initHeadFont();
		initContentCellStyle();
		initContentFont();
	}

	/**
	 * 自动调整列宽
	 */
	private static void adjustColumnSize(SheetInfo sheetInfo, HSSFSheet sheet) {
		for (int i = 0; i < sheetInfo.getHeadNames().size() + 1; i++) {
			sheet.autoSizeColumn(i, true);
		}
	}

	/**
	 * 创建标题行(需合并单元格)
	 */
	private static void createTableTitleRow(SheetInfo sheetInfo, HSSFSheet sheet) {
		CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, sheetInfo.getHeadNames().size());
		sheet.addMergedRegion(titleRange);
		HSSFRow titleRow = sheet.createRow(0);
		titleRow.setHeight((short) 800);
		HSSFCell titleCell = titleRow.createCell(0);
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue(sheetInfo.getTitle());
	}

	/**
	 * 创建日期行(需合并单元格)
	 */
	private static void createTableDateRow(SheetInfo sheetInfo, HSSFSheet sheet) {
		CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, sheetInfo.getHeadNames().size());
		sheet.addMergedRegion(dateRange);
		HSSFRow dateRow = sheet.createRow(1);
		dateRow.setHeight((short) 350);
		HSSFCell dateCell = dateRow.createCell(0);
		dateCell.setCellStyle(dateStyle);
		dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
	}

	/**
	 * 创建表头行(需合并单元格)
	 */
	private static void creatTableHeadRow(SheetInfo sheetInfo, HSSFSheet sheet) {
		// 表头
		HSSFRow headRow = sheet.createRow(2);
		headRow.setHeight((short) 350);
		// 序号列
		HSSFCell snCell = headRow.createCell(0);
		snCell.setCellStyle(headStyle);
		snCell.setCellValue("序号");
		// 列头名称
		for (int num = 1, len = sheetInfo.getHeadNames().size(); num <= len; num++) {
			HSSFCell headCell = headRow.createCell(num);
			headCell.setCellStyle(headStyle);
			headCell.setCellValue(sheetInfo.getHeadNames().get(num - 1));
		}
	}

	/**
	 * 创建所有的Sheet
	 */
	private static HSSFSheet[] getSheets(List<SheetInfo> sheetInfos) {
		HSSFSheet[] sheets = new HSSFSheet[sheetInfos.size()];
		for (int i = 0; i < sheetInfos.size(); i++) {
			sheets[i] = wb.createSheet(sheetInfos.get(i).getSheetName());
		}
		return sheets;
	}

	/**
	 * 创建内容行的每一列(附加一列序号)
	 */
	private static HSSFCell[] getCells(HSSFRow contentRow, int num) {
		HSSFCell[] cells = new HSSFCell[num + 1];

		for (int i = 0, len = cells.length; i < len; i++) {
			cells[i] = contentRow.createCell(i);
			cells[i].setCellStyle(contentStyle);
		}
		// 设置序号列值，因为出去标题行和日期行，所有-2
		cells[0].setCellValue(contentRow.getRowNum() - 2);

		return cells;
	}

	/**
	 * 初始化标题行样式
	 */
	private static void initTitleCellStyle() {
		titleStyle.setAlignment(HorizontalAlignment.CENTER);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		titleStyle.setFont(titleFont);
		titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex());
	}

	/**
	 * 初始化日期行样式
	 */
	private static void initDateCellStyle() {
		dateStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		dateStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		dateStyle.setFont(dateFont);
		dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.getIndex());
	}

	/**
	 * 初始化表头行样式
	 */
	private static void initHeadCellStyle() {
		headStyle.setAlignment(HorizontalAlignment.CENTER);
		headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		headStyle.setFont(headFont);
		headStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
		headStyle.setBorderTop(BorderStyle.MEDIUM);
		headStyle.setBorderBottom(BorderStyle.THIN);
		headStyle.setBorderLeft(BorderStyle.THIN);
		headStyle.setBorderRight(BorderStyle.THIN);
		headStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
		headStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
		headStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
		headStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
	}

	/**
	 * 初始化内容行样式
	 */
	private static void initContentCellStyle() {
		contentStyle.setAlignment(HorizontalAlignment.CENTER);
		contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		contentStyle.setFont(contentFont);
		contentStyle.setBorderTop(BorderStyle.THIN);
		contentStyle.setBorderBottom(BorderStyle.THIN);
		contentStyle.setBorderLeft(BorderStyle.THIN);
		contentStyle.setBorderRight(BorderStyle.THIN);
		contentStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
		contentStyle.setWrapText(true);    // 字段换行
	}

	/**
	 * 初始化标题行字体
	 */
	private static void initTitleFont() {
		titleFont.setFontName("华文楷体");
		titleFont.setFontHeightInPoints((short) 20);
		titleFont.setBold(true);
		titleFont.setCharSet(Font.DEFAULT_CHARSET);
		titleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}

	/**
	 * 初始化日期行字体
	 */
	private static void initDateFont() {
		dateFont.setFontName("隶书");
		dateFont.setFontHeightInPoints((short) 10);
		dateFont.setBold(true);
		dateFont.setCharSet(Font.DEFAULT_CHARSET);
		dateFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}

	/**
	 * 初始化表头行字体
	 */
	private static void initHeadFont() {
		headFont.setFontName("宋体");
		headFont.setFontHeightInPoints((short) 10);
		headFont.setBold(true);
		headFont.setCharSet(Font.DEFAULT_CHARSET);
		headFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}

	/**
	 * 初始化内容行字体
	 */
	private static void initContentFont() {
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 10);
		contentFont.setBold(true);
		contentFont.setCharSet(Font.DEFAULT_CHARSET);
		contentFont.setColor(IndexedColors.BLUE_GREY.getIndex());
	}

	@Data
	public static class SheetInfo {
		private String sheetName;
		private String title;
		private List<String> headNames;
		private List<String> fieldNames;
		private List datas;
	}

}